--    Author    : XUEZHOUYI
--    Name      : ODS.HBASE_LOAF.HQL
--    Functions : 
--    Purpose   : 
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-10  XUEZHOUYI       记录有可能重复，为了避免重复记录有同样的MD5需要衍生一列序号来区分
--

INSERT OVERWRITE TABLE ODS.HBASE_LOAF
SELECT
     F_CREATEMD5(TMP.RN,TMP.DATA_DATE,TMP.AREA_NO_ID,TMP.DATA_SRC_ORG,TMP.DATA_ORG_CODE,TMP.CUSTOMER_TYPE,TMP.CUSTOMER_ID,TMP.LPIC,TMP.REGIST_ADD_CODE,TMP.ENTERPRISE_INVESTOR,TMP.ENTERPRISE_SCALE,TMP.LOAN_IOUS_CODE,TMP.PRODUCT_CATEGORY,TMP.LOANS_ACTUALLY,TMP.LENDING_DATE,TMP.LOAN_DUE_DATE,TMP.LOAN_ACTUAL_END_DATE,TMP.TERM,TMP.LOAN_CUR_TYPE,TMP.LOAN_OCCURRENCE,TMP.RATE_FIXED_FLOAT,TMP.RATE_LEVEL,TMP.GUARANTY_STYLE,TMP.LOAN_STATUS,TMP.LOAN_GRANT_RECOVER) AS UUID
    ,TMP.DATA_DATE           
    ,TMP.AREA_NO_ID           
    ,TMP.DATA_SRC_ORG        
    ,TMP.DATA_ORG_CODE       
    ,TMP.CUSTOMER_TYPE       
    ,TMP.CUSTOMER_ID         
    ,TMP.LPIC            
    ,TMP.REGIST_ADD_CODE     
    ,TMP.ENTERPRISE_INVESTOR 
    ,TMP.ENTERPRISE_SCALE    
    ,TMP.LOAN_IOUS_CODE
    ,TMP.PRODUCT_CATEGORY    
    ,TMP.LOANS_ACTUALLY      
    ,TMP.LENDING_DATE        
    ,TMP.LOAN_DUE_DATE       
    ,TMP.LOAN_ACTUAL_END_DATE
    ,TMP.TERM
    ,TMP.LOAN_CUR_TYPE       
    ,TMP.LOAN_OCCURRENCE     
    ,TMP.RATE_FIXED_FLOAT    
    ,TMP.RATE_LEVEL          
    ,TMP.GUARANTY_STYLE      
    ,TMP.LOAN_STATUS         
    ,TMP.LOAN_GRANT_RECOVER  
FROM(
    SELECT
         ROW_NUMBER() OVER(PARTITION BY LAF.DATA_DATE,ORG.DATA_ORG_AREA,LAF.DATA_SRC_ORG,LAF.DATA_ORG_CODE,LAF.CUSTOMER_TYPE,LAF.CUSTOMER_ID,LAF.LPIC,LAF.REGIST_ADD_CODE,LAF.ENTERPRISE_INVESTOR,LAF.ENTERPRISE_SCALE,LAF.LOAN_IOUS_CODE,LAF.PRODUCT_CATEGORY,LAF.LOANS_ACTUALLY,LAF.LENDING_DATE,LAF.LOAN_DUE_DATE,LAF.LOAN_ACTUAL_END_DATE,LAF.LOAN_CUR_TYPE,LAF.LOAN_OCCURRENCE,LAF.RATE_FIXED_FLOAT,LAF.RATE_LEVEL,LAF.GUARANTY_STYLE,LAF.LOAN_STATUS,LAF.LOAN_GRANT_RECOVER) AS RN
        ,LAF.DATA_DATE           
        ,COALESCE(ORG.DATA_ORG_AREA,'999999') AS AREA_NO_ID
        ,LAF.DATA_SRC_ORG        
        ,LAF.DATA_ORG_CODE       
        ,LAF.CUSTOMER_TYPE       
        ,LAF.CUSTOMER_ID         
        ,LAF.LPIC            
        ,LAF.REGIST_ADD_CODE     
        ,LAF.ENTERPRISE_INVESTOR 
        ,LAF.ENTERPRISE_SCALE    
        ,LAF.LOAN_IOUS_CODE      
        ,LAF.PRODUCT_CATEGORY    
        ,LAF.LOANS_ACTUALLY      
        ,LAF.LENDING_DATE        
        ,LAF.LOAN_DUE_DATE       
        ,LAF.LOAN_ACTUAL_END_DATE
        ,CEIL(MONTHS_BETWEEN(LAF.LOAN_DUE_DATE,LAF.LENDING_DATE)) AS TERM
        ,LAF.LOAN_CUR_TYPE       
        ,LAF.LOAN_OCCURRENCE     
        ,LAF.RATE_FIXED_FLOAT    
        ,LAF.RATE_LEVEL          
        ,LAF.GUARANTY_STYLE      
        ,LAF.LOAN_STATUS         
        ,LAF.LOAN_GRANT_RECOVER  
    FROM ODS.LOAF LAF LEFT JOIN DMCODE.T_ORG_MANAGE ORG ON LAF.DATA_ORG_CODE = ORG.DATA_ORG_ID
    WHERE LAF.DATA_DATE IN('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_2M_END_DATE#')
)TMP
;